 use newBiz
 GO


 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'MemberCLevel')
	BEGIN
		DROP  Table [dbo].[MemberCLevel]
	END
GO

CREATE TABLE [dbo].[MemberCLevel](
	[MemberID]  [varchar](20)  NOT NULL,
	[AgentID]  [varchar](20)  NOT NULL,
	[MemberPlan] [varchar](20) NULL,
	[Level] int default 0 NULL,
	[CLevel] int default 0 NULL
) ON [PRIMARY]
GO




 IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'Process_Member')
	BEGIN
		DROP  procedure [dbo].Process_Member
	END
GO 

create procedure Process_Member
    @id varchar(20)
AS
		declare @date datetime
		select @date = DateRegistered from Member where memberid = @id

		-- Get agent with sales
		--select distinct agent from Member
		--where Agent in
		--(select agent from MemberLevel where memberid = @id)
		--and month(DateRegistered) = month(@date)
		--and year(DateRegistered) = year(@date)

		delete from MemberCLevel where memberid = @id

		insert into MemberCLevel(CLevel, MemberID, AgentID, [Level])
		SELECT ROW_NUMBER() OVER(ORDER BY level) AS cLevel,@id, agentid, level
		from MemberLevel where MemberID = @id 
		and AgentID in 
				(select distinct agent from Member -- get agents with sales
				where Agent in
				(select agent from MemberLevel where memberid = @id)
				and month(DateRegistered) = month(@date)
				and year(DateRegistered) = year(@date))

       update MemberLevel set CLevel = 0 where MemberID = @id
     
        -- update memberlevel from memberclevel
		--update member
GO
 